package com.scalar.chiptrack.operations.wipflush.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedHashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.ProcessStepCostInfo;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.wipflush.WipFlushBinInfo;
import com.scalar.chiptrack.operations.wipflush.WipFlushDetails;
import com.scalar.chiptrack.operations.wipflush.forms.WipFlushSetupForm;
import com.scalar.chiptrack.utils.AutoGrowingArrayList;
import com.scalar.chiptrack.utils.NewLabelValueBean;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * User: Administrator
 * Date: Oct 24, 2005
 * Time: 1:58:45 PM
 */
public class WipFlushDetailDAO  implements Constants
{
    public static LinkedHashMap loadProductFamilyDetails( DBTransaction dbTransaction, int baseProductId )  throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt	= null;
        ResultSet rs 			= null;
        ArrayList productList = new ArrayList();
        WipFlushDetails wipFlushDetails = null;
        LinkedHashMap processProductsMap = new LinkedHashMap();
        String  psTypeName = null;
        Calendar now = Calendar.getInstance();
        int currentQtr = ( now.get ( Calendar.MONTH )/3) + 1; //CURRENT QUARTER STARTS FROM ONE
        int currentYear = now.get( Calendar.YEAR );
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT PS_TYPE_NAME, IS_LEAVE_SPACE, PROCESS_STEP_ID, PRODUCT_NAME, PACKAGE_ID, CYCLE_TIME_NORMAL, YIELD, LOCATION_ID FROM FN_GET_BASE_PRODUCT_PROCESS_FLOW ( ?, ?, ? ) ORDER BY PACKAGE_ID_ORDER, PATH_NUMBER, PS_TYPE_NAME_ORDER DESC" );
            pstmt.setInt( 1, baseProductId );
            pstmt.setInt ( 2, currentQtr );
            pstmt.setInt ( 3, currentYear );
            rs 	  = pstmt.executeQuery();
            boolean isLeaveSpace;
            while( rs.next() )
            {
                psTypeName = rs.getString( "PS_TYPE_NAME" );
                isLeaveSpace = ( rs.getInt ( "IS_LEAVE_SPACE" ) == 0  );
                productList = ( ArrayList ) processProductsMap.get( psTypeName );
                if( productList == null )
                {
                    productList = new ArrayList();
                    processProductsMap.put ( psTypeName, productList );
                }
                wipFlushDetails = new WipFlushDetails();
                wipFlushDetails.setProcessStepId( rs.getInt( "PROCESS_STEP_ID" ) );
                wipFlushDetails.setPartNumber( rs.getString( "PRODUCT_NAME" ) );
                wipFlushDetails.setProcessTypeName( psTypeName );

                if ( !productList.contains( wipFlushDetails ) )
                {
                    wipFlushDetails.setLeaveSpace( isLeaveSpace );
                    wipFlushDetails.setPackageId( rs.getString ( "PACKAGE_ID" ));
                    int cycleTimeNormal = rs.getInt ( "CYCLE_TIME_NORMAL" );
                    wipFlushDetails.setCycleTimeNormal( (cycleTimeNormal<0) ? 0 : cycleTimeNormal);
                    float yield = rs.getFloat ( "YIELD" );
                    wipFlushDetails.setYield( (yield==0) ? 100 : yield );
                    wipFlushDetails.setLocationId( rs.getInt ( "LOCATION_ID" ));
                    productList.add( wipFlushDetails );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Product Family Information could not be fetched" );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return processProductsMap;
    }

    public static void loadProcessStepBinDetails ( DBTransaction dbTransaction, WipFlushSetupForm wipFlushSetupForm ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;

        boolean success = false;
        wipFlushSetupForm.setBinNumbers( getProcessStepBinNumbers(dbTransaction, wipFlushSetupForm.getSelectedProcessStepId() ) );
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = ?");
            cstmt.setInt(1, wipFlushSetupForm.getSelectedProcessStepId());
            rset = cstmt.executeQuery();
            if ( rset.next() )
            {
                wipFlushSetupForm.setProcessTypeName( rset.getString ( "PS_TYPE_NAME" ));
            }

            try{ rset.close(); } catch( SQLException ignore ){}
            try{ cstmt.close(); } catch( SQLException ignore ){}

            //TO LOAD BIN INFO
            cstmt = conn.prepareCall( "SELECT * FROM FN_LOAD_WIP_FLUSH_BIN_INFO(?)");
            cstmt.setInt ( 1, wipFlushSetupForm.getSelectedProcessStepId() );
            rset = cstmt.executeQuery();
            AutoGrowingArrayList binInfoList = new AutoGrowingArrayList( WipFlushBinInfo.class );
            WipFlushBinInfo wipFlushBinInfo = null;
            while ( rset.next() )
            {
                wipFlushBinInfo = new WipFlushBinInfo();
                wipFlushBinInfo.setBinNumber( rset.getString ( "BIN_NUMBER" ) );
                wipFlushBinInfo.setProcessStepId( rset.getInt ( "PROCESS_STEP_ID" ));
                wipFlushBinInfo.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                wipFlushBinInfo.setBinValue( rset.getFloat ( "BIN_VALUE" ) );
                binInfoList.add ( wipFlushBinInfo );
            }
            wipFlushSetupForm.setBinInfoList( binInfoList );
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not fetch process step details", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch( SQLException e ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }

    public static void saveWipFlushSetup( DBTransaction dbTransaction, WipFlushSetupForm wipFlushSetupForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        boolean success = false;

        Calendar now = Calendar.getInstance();
        int currentQtr = (now.get(Calendar.MONTH)/3) + 1;//CURRENT QTR STARTS FROM ONE
        int currentYear = now.get( Calendar.YEAR );
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            ArrayList wipFlushDetailsList = wipFlushSetupForm.getWipFlushDetailsList();
            int wipFlushDetailsListSize = (wipFlushDetailsList==null) ? 0 : wipFlushDetailsList.size();
            pstmt = conn.prepareStatement ( "UPDATE Process_Step_Location_Table SET PSLT_Cycle_Time_Normal = ? WHERE ProcessStep_ID = ? AND Location_ID = ?");
            WipFlushDetails wipFlushDetails;
            for ( int i=0; i < wipFlushDetailsListSize; i++ )
            {
                wipFlushDetails = (WipFlushDetails)wipFlushDetailsList.get(i);
                pstmt.setInt ( 1, wipFlushDetails.getCycleTimeNormal() );
                pstmt.setInt( 2, wipFlushDetails.getProcessStepId() );
                pstmt.setInt( 3, wipFlushDetails.getLocationId() );

                pstmt.addBatch();
            }
            if ( wipFlushDetailsListSize > 0 )
            {
                pstmt.executeBatch();
            }

            try{ pstmt.close(); } catch ( SQLException ignore ){}
            for ( int i=0; i < wipFlushDetailsListSize; i++ )
            {
                wipFlushDetails = (WipFlushDetails)wipFlushDetailsList.get(i);
                pstmt = conn.prepareStatement ( "SELECT ProcessStep_id, Location_id, Q1_Standard_Cost, Q1_Fixed_Over_head_Cost, Q1_Variable_Over_head_cost, Q1_Misc_Cost, Q1_Yield, Q2_Standard_Cost, Q2_Fixed_Over_head_Cost, Q2_Variable_Over_head_cost, Q2_Misc_Cost, Q2_Yield, Q3_Standard_Cost, Q3_Fixed_Over_head_Cost, Q3_Variable_Over_head_cost, Q3_Misc_Cost, Q3_Yield, Q4_Standard_Cost, Q4_Fixed_Over_head_Cost, Q4_Variable_Over_head_cost, Q4_Misc_Cost, Q4_Yield, Fin_Year, Timestamp FROM update_view_cost_table WHERE ProcessStep_id=? AND Location_Id=? AND year(Fin_Year)=?");
                pstmt.setInt ( 1, wipFlushDetails.getProcessStepId() );
                pstmt.setInt ( 2, wipFlushDetails.getLocationId() );
                pstmt.setInt ( 3, currentYear );
                rset = pstmt.executeQuery();
                ProcessStepCostInfo processStepCostInfo = new ProcessStepCostInfo ();
                if ( rset.next() )
                {
                    processStepCostInfo.setProcessStepId( rset.getInt ( "ProcessStep_ID" ) );
                    processStepCostInfo.setLocationId( rset.getInt( "location_id" ) );

                    processStepCostInfo.setQ1StandardCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q1_Standard_Cost" )), "###.##") );
                    processStepCostInfo.setQ1FixedOverheadCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q1_Fixed_Over_head_Cost" )), "###.##") );
                    processStepCostInfo.setQ1VariableOverheadcost( StringUtils.formatAmount((float)(rset.getDouble ( "Q1_Variable_Over_head_cost" )), "###.##") );
                    processStepCostInfo.setQ1MiscCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q1_Misc_Cost" )), "###.##") );
                    processStepCostInfo.setQ1Yield( StringUtils.formatAmount((float)(rset.getDouble ( "Q1_Yield" )), "###.##") );

                    processStepCostInfo.setQ2StandardCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q2_Standard_Cost" )), "###.##") );
                    processStepCostInfo.setQ2FixedOverheadCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q2_Fixed_Over_head_Cost" )), "###.##") );
                    processStepCostInfo.setQ2VariableOverheadcost( StringUtils.formatAmount((float)(rset.getDouble ( "Q2_Variable_Over_head_cost" )), "###.##") );
                    processStepCostInfo.setQ2MiscCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q2_Misc_Cost" )), "###.##") );
                    processStepCostInfo.setQ2Yield( StringUtils.formatAmount((float)(rset.getDouble ( "Q2_Yield" )), "###.##") );

                    processStepCostInfo.setQ3StandardCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q3_Standard_Cost" )), "###.##") );
                    processStepCostInfo.setQ3FixedOverheadCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q3_Fixed_Over_head_Cost" )), "###.##") );
                    processStepCostInfo.setQ3VariableOverheadcost( StringUtils.formatAmount((float)(rset.getDouble ( "Q3_Variable_Over_head_cost" )), "###.##") );
                    processStepCostInfo.setQ3MiscCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q3_Misc_Cost" )), "###.##") );
                    processStepCostInfo.setQ3Yield( StringUtils.formatAmount((float)(rset.getDouble ( "Q3_Yield" )), "###.##") );

                    processStepCostInfo.setQ4StandardCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q4_Standard_Cost" )), "###.##") );
                    processStepCostInfo.setQ4FixedOverheadCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q4_Fixed_Over_head_Cost" )), "###.##") );
                    processStepCostInfo.setQ4VariableOverheadcost( StringUtils.formatAmount((float)(rset.getDouble ( "Q4_Variable_Over_head_cost" )), "###.##") );
                    processStepCostInfo.setQ4MiscCost( StringUtils.formatAmount((float)(rset.getDouble ( "Q4_Misc_Cost" )), "###.##") );
                    processStepCostInfo.setQ4Yield( StringUtils.formatAmount((float)(rset.getDouble ( "Q4_Yield" )), "###.##") );
                }
                try{ rset.close(); } catch ( SQLException ignore ) {}
                try{ pstmt.close(); } catch ( SQLException ignore ){}

                pstmt = conn.prepareStatement ( "{Call sp_create_update_view_cost(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }" );

                pstmt.setInt ( 1, wipFlushDetails.getProcessStepId() );
                pstmt.setInt ( 2, wipFlushDetails.getLocationId() );

                pstmt.setString ( 3, (processStepCostInfo.getQ1StandardCost()==null)?"0":processStepCostInfo.getQ1StandardCost().trim() );
                pstmt.setString ( 4, (processStepCostInfo.getQ1FixedOverheadCost()==null)?"0":processStepCostInfo.getQ1FixedOverheadCost().trim() );
                pstmt.setString ( 5, (processStepCostInfo.getQ1VariableOverheadcost()==null)?"0":processStepCostInfo.getQ1VariableOverheadcost().trim() );
                pstmt.setString ( 6, (processStepCostInfo.getQ1MiscCost()==null)?"0":processStepCostInfo.getQ1MiscCost().trim() );
                pstmt.setFloat ( 7, ( currentQtr == 1 ) ? wipFlushDetails.getYield() : 0 );

                pstmt.setString ( 8, (processStepCostInfo.getQ2StandardCost()==null)?"0":processStepCostInfo.getQ2StandardCost().trim() );
                pstmt.setString ( 9, (processStepCostInfo.getQ2FixedOverheadCost()==null)?"0":processStepCostInfo.getQ2FixedOverheadCost().trim() );
                pstmt.setString ( 10, (processStepCostInfo.getQ2VariableOverheadcost()==null)?"0":processStepCostInfo.getQ2VariableOverheadcost().trim() );
                pstmt.setString ( 11, (processStepCostInfo.getQ2MiscCost()==null)?"0":processStepCostInfo.getQ2MiscCost().trim() );
                pstmt.setFloat ( 12, ( currentQtr == 2 ) ? wipFlushDetails.getYield() : 0 );

                pstmt.setString ( 13, (processStepCostInfo.getQ3StandardCost()==null)?"0":processStepCostInfo.getQ3StandardCost().trim() );
                pstmt.setString ( 14, (processStepCostInfo.getQ3FixedOverheadCost()==null)?"0":processStepCostInfo.getQ3FixedOverheadCost().trim() );
                pstmt.setString ( 15, (processStepCostInfo.getQ3VariableOverheadcost()==null)?"0":processStepCostInfo.getQ3VariableOverheadcost().trim() );
                pstmt.setString ( 16, (processStepCostInfo.getQ3MiscCost()==null)?"0":processStepCostInfo.getQ3MiscCost().trim() );
                pstmt.setFloat ( 17, ( currentQtr == 3 ) ? wipFlushDetails.getYield() : 0 );

                pstmt.setString ( 18, (processStepCostInfo.getQ4StandardCost()==null)?"0":processStepCostInfo.getQ4StandardCost().trim() );
                pstmt.setString ( 19, (processStepCostInfo.getQ4FixedOverheadCost()==null)?"0":processStepCostInfo.getQ4FixedOverheadCost().trim() );
                pstmt.setString ( 20, (processStepCostInfo.getQ4VariableOverheadcost()==null)?"0":processStepCostInfo.getQ4VariableOverheadcost().trim() );
                pstmt.setString ( 21, (processStepCostInfo.getQ4MiscCost()==null)?"0":processStepCostInfo.getQ4MiscCost().trim() );
                pstmt.setFloat ( 22, ( currentQtr == 4 ) ? wipFlushDetails.getYield() : 0 );
                pstmt.setFloat ( 23, currentYear );

                pstmt.executeUpdate();

                try{ pstmt.close(); } catch ( SQLException ignore ){}
            }

            pstmt = conn.prepareStatement ( "DELETE FROM Wip_Flush_Bin_Setup_Table WHERE ProcessStep_ID = ?" );
            pstmt.setInt ( 1, wipFlushSetupForm.getSelectedProcessStepId() );

            pstmt.executeUpdate();

            try{ pstmt.close(); } catch ( SQLException ignore ){}

            pstmt = conn.prepareStatement ( "INSERT INTO Wip_Flush_Bin_Setup_Table(ProcessStep_ID,Next_ProcessStep_ID,Bin_Number,Bin_Value,Timestamp) VALUES ( ?, ?, ?, ?, CURRENT_TIMESTAMP )");
            pstmt.setInt ( 1, wipFlushSetupForm.getSelectedProcessStepId() );

            ArrayList binInfoList = wipFlushSetupForm.getBinInfoList();
            WipFlushBinInfo wipFlushBinInfo = null;
            int binInfoListSize = ( binInfoList != null ) ? binInfoList.size() : 0;
            for ( int i=0; i < binInfoListSize; i++ )
            {
                wipFlushBinInfo = ( WipFlushBinInfo) binInfoList.get ( i );
                pstmt.setInt ( 2, wipFlushBinInfo.getProcessStepId() );
                pstmt.setString( 3, wipFlushBinInfo.getBinNumber() );
                pstmt.setFloat( 4, wipFlushBinInfo.getBinValue() );

                pstmt.addBatch();
            }

            if ( binInfoListSize > 0 )
            {
                pstmt.executeBatch();
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not save WipFlush Setup", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try{ conn.commit(); } catch ( SQLException e ) { e.printStackTrace(); }
                    }
                    else
                    {
                        try{ conn.rollback(); } catch ( SQLException e ) { e.printStackTrace(); }
                    }
                }
            }
            if( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static void loadFormDetails ( DBTransaction dbTransaction, WipFlushSetupForm wipFlushSetupForm ) throws ScalarException
    {
        String baseProductName = ProductDao.getBaseProductName(Integer.toString(wipFlushSetupForm.getBaseProductId()));
        wipFlushSetupForm.setBaseProductName(baseProductName);

        String selectedPartNumber = ProductDao.getDeviceName( wipFlushSetupForm.getSelectedProcessStepId() );
        wipFlushSetupForm.setPartNumber( selectedPartNumber);
    }
    public static int getCurrentQtr()
    {
        Calendar now = Calendar.getInstance();
        return ( (now.get(Calendar.MONTH)/3) + 1 );
    }
    public static AutoGrowingArrayList getProcessStepBinNumbers( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean success = false;
        AutoGrowingArrayList binNumbers = new AutoGrowingArrayList( NewLabelValueBean.class );
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "SELECT * FROM FN_LOAD_PROCESS_STEP_BIN_NUMBERS(?) ORDER BY CONVERT(INT,SUBSTRING(BIN_NUMBER,4,LEN(LTRIM(RTRIM(BIN_NUMBER)))))");
            cstmt.setInt ( 1, processStepId );
            rset = cstmt.executeQuery();

            String binNumber;
            while ( rset.next() )
            {
                binNumber = rset.getString ( "BIN_NUMBER" );
                binNumbers.add ( new NewLabelValueBean ( binNumber, binNumber ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException("Could not fetch bin numbers", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try{ conn.commit(); } catch ( SQLException e ) { }
                    }
                    else
                    {
                        try{ conn.rollback(); } catch ( SQLException e ) { }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return binNumbers;
    }

    public static AutoGrowingArrayList getWipFlushDetailsListFromMap ( LinkedHashMap productFamilyDetailsMap )
    {
        AutoGrowingArrayList wipFlushDetailsList = new AutoGrowingArrayList( WipFlushDetails.class );
        ArrayList processList = null;
        int processListSize = 0;
        if ( productFamilyDetailsMap != null )
        {
            Iterator itr = productFamilyDetailsMap.keySet().iterator();
            while ( itr.hasNext() )
            {
                processList = (ArrayList)productFamilyDetailsMap.get ( itr.next() );
                processListSize = ( processList == null ) ? 0 : processList.size();
                for ( int i=0; i < processListSize; i++ )
                {
                    wipFlushDetailsList.add (  processList.get(i) );
                }
            }
        }
        return wipFlushDetailsList;
    }
}
